const Database = require('better-sqlite3');
const db = new Database('data/delivery-management.db');

// Check parameter tables with data
const paramTables = ['table_c2', 'table_c494', 'table_c6'];

console.log('Examining parameter table data...');

paramTables.forEach(tableName => {
  try {
    console.log(`\n=== ${tableName} ===`);
    const structure = db.prepare(`PRAGMA table_info([${tableName}])`).all();
    console.log('Columns:', structure.map(col => `${col.name} (${col.type})`).join(', '));
    
    const records = db.prepare(`SELECT * FROM [${tableName}]`).all();
    console.log('Records:', records.length);
    
    if (records.length > 0) {
      console.log('First record:');
      const first = records[0];
      Object.keys(first).forEach(key => {
        if (first[key] !== null && first[key] !== '') {
          console.log(`  ${key}: ${first[key]}`);
        }
      });
    }
  } catch (error) {
    console.log(`Error with ${tableName}: ${error.message}`);
  }
});

// Check if there's any link between parameter records and device instances
console.log('\n=== Checking for device instances that might reference parameter records ===');

// Look for any instance tables that have records with record_id fields
const instanceTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'device_instances_%'").all();

instanceTables.slice(0, 10).forEach(table => {
  try {
    const structure = db.prepare(`PRAGMA table_info([${table.name}])`).all();
    const hasRecordIds = structure.some(col => col.name.includes('record_id'));
    
    if (hasRecordIds) {
      console.log(`${table.name} has record_id columns`);
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      console.log(`  Records: ${count.count}`);
    }
  } catch (error) {
    // Skip error tables
  }
});

db.close();